package cn.qayy.utils;

import java.math.RoundingMode;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.StringUtils;

import cn.qayy.constants.Constant;
import cn.qayy.dto.PolicyIdItem;
import cn.qayy.po.Plan;
import cn.qayy.po.Policy;
import cn.qayy.po.Task;

/****
 * 定时任务
 * 
 * @author jobs1127
 *
 */
public class ExpireJobTask {
	public void doBiz() {
		System.out.println("定时任务开始……");
		long start1 = System.currentTimeMillis();
		long start = System.currentTimeMillis();
		update_zck_plans_policy();// 定时基础库更新注册库、注册库更新流向表
		long end = System.currentTimeMillis();
		logs("基础库更新注册库、注册库更新流向表","成功执行",(end-start)/1000+"秒");
		
		start = System.currentTimeMillis();
		tongbu();// 同步重点终端和好伙伴,同步更新销售折量
		end = System.currentTimeMillis();
		logs("同步重点终端和好伙伴,同步更新销售折量","成功执行",(end-start)/1000+"秒");
		
		start = System.currentTimeMillis();
		tongbu_cost();// 同步更新产品成本
		end = System.currentTimeMillis();
		logs("同步更新产品成本","成功执行",(end-start)/1000+"秒");
		
		start = System.currentTimeMillis();
		systemAutoPPPolicy();// 定时流向匹配4/5/6状态协议
		end = System.currentTimeMillis();
		logs("定时流向匹配4/5/6状态协议","成功执行",(end-start)/1000+"秒");
		
		start = System.currentTimeMillis();
		systemAutoPPNoPolicy();// 定时流向匹配自然流，流向表里存在的协议id，如果在协议表里找不到，则匹配成自然流向
		logs("定时流向匹配自然流 ","成功执行",(end-start)/1000+"秒");
		end = System.currentTimeMillis();
		
		start = System.currentTimeMillis();
		systemAutoPPPolicyById();// 根据协议id刷新没毕的流向，保障手动匹配的流向和销售流向政策和协议统一
		end = System.currentTimeMillis();
		logs("根据协议id刷新没毕的流向","成功执行",(end-start)/1000+"秒");
		
		start = System.currentTimeMillis();
		y19Task();// 定时计算Y19重点客户跟进情况
		logs("定时计算Y19重点客户跟进情况 ","成功执行",(end-start)/1000+"秒");
		end = System.currentTimeMillis();
		
		
		
		logs("系统定时同步更新","成功执行",(end-start1)/1000/60+"分");
	}
	
	/***
	 * 定时计算Y19重点客户跟进情况
	 * 初始化当前季度实际纯销，比如Y19Q1、上季度实际纯销
	 */
	private void y19Task() {
		try {
			QueryRunner query = new QueryRunner(DataSourceUtils.getDataSource());
//			String version = (String)query.query("select version from task limit 1", new ScalarHandler());
//			if(StringUtil.isBlank(version)) {
//				version = "Y2018Q3";
//			}
			String sql= "select id,client client,product product,page page,terminal_qs terminalQs from task";
			//获取待更新的Task
			List<Task> list = query.query(sql, new BeanListHandler<Task>(Task.class));
			//当前季度
			String v = DateUtils.getPreVersion(DateUtils.getCurVersion());
			String targetV = DateUtils.getCurVersion();//目标纯销量 对应的版本
			String year = v.substring(1,5);
			String q = v.substring(5);
			
			String target_year = targetV.substring(1,5);
			String target_q = targetV.substring(5);
			
			String start = null;
			String end = null;
			String target_start = null;
			String target_end = null;
			
			String cm1 = null;
			String cm2 = null;
			String cm3 = null;
			if("Q1".equals(q)) {
				start = year+"/01/01";
				end = year+"/03/31";
				cm1 = "01";
				cm2 = "02";
				cm3 = "03";
			}
			if("Q2".equals(q)) {
				start = year+"/04/01";
				end = year+"/06/30";
				cm1 = "04";
				cm2 = "05";
				cm3 = "06";
			}
			if("Q3".equals(q)) {
				start = year+"/07/01";
				end = year+"/09/30";
				cm1 = "07";
				cm2 = "08";
				cm3 = "09";
			}
			if("Q4".equals(q)) {
				start = year+"/10/01";
				end = year+"/12/31";
				cm1 = "10";
				cm2 = "11";
				cm3 = "12";
			}
			
			//当前季度实际纯销
			if("Q1".equals(target_q)) {
				target_start = target_year+"/01/01";
				target_end = target_year+"/03/31";
			}
			if("Q2".equals(target_q)) {
				target_start = target_year+"/04/01";
				target_end = target_year+"/06/30";
			}
			if("Q3".equals(target_q)) {
				target_start = target_year+"/07/01";
				target_end = target_year+"/09/30";
			}
			if("Q4".equals(target_q)) {
				target_start = target_year+"/10/01";
				target_end = target_year+"/12/31";
			}
			
			String query_sql = "select client,product,pack page,zhenghe_qs terminalQs,sum(count) taskM1,mouth taskM2,atsum policyPrice "
					+ " from kdb_nss_log_plans p "
					+ " where sdate >= '"+start+"' and sdate <= '"+end+"' and client <> 'QA' and client is not null and judge = '纯销' "
					+ " group by client,product,pack,zhenghe_qs,mouth";
			List<Task> mlist = query.query(query_sql, new BeanListHandler<Task>(Task.class));
			
			String target_query_sql = "select client,product,pack page,zhenghe_qs terminalQs,sum(count) taskM1 "
					+ " from kdb_nss_log_plans p "
					+ " where sdate >= '"+target_start+"' and sdate <= '"+target_end+"' and client <> 'QA' and client is not null and judge = '纯销' "
					+ " group by client,product,pack,zhenghe_qs";
			List<Task> target_mlist = query.query(target_query_sql, new BeanListHandler<Task>(Task.class));
			
			Object[][] params = new Object[list.size()][];
			Object[][] target_params = new Object[list.size()][];
			for (int i=0;i<list.size();i++) {
				Task t = list.get(i);
				Integer id = t.getId();
				String policyPrice = "0";
				String m1 = "0";
				String m2 = "0";
				String m3 = "0";
				for (Task mt : mlist) {
					if(mt.getClient().equals(t.getClient()) &&
							mt.getProduct().equals(t.getProduct()) &&
							mt.getPage().equals(t.getPage()) &&
							mt.getTerminalQs().equals(t.getTerminalQs())) {
						policyPrice = mt.getPolicyPrice();//每次覆盖，最后一个则为最新的
						if(Integer.parseInt(cm1) == Integer.parseInt(mt.getTaskM2())) {//taskM2 充当月份
							m1 = mt.getTaskM1();
						}
						if(Integer.parseInt(cm2) == Integer.parseInt(mt.getTaskM2())) {//taskM2 充当月份
							m2 = mt.getTaskM1();
						}
						if(Integer.parseInt(cm3) == Integer.parseInt(mt.getTaskM2())) {//taskM2 充当月份
							m3 = mt.getTaskM1();
						}
					}
				}
				params[i] = new Object[] {policyPrice,m1,m2,m3,id};
			}
			String update = "update task set policy_price = ?,m1=?,m2=?,m3=? where id=?";
			query.batch(update, params);
			
			//当前季度实际纯销------------
			for (int i=0;i<list.size();i++) {
				Task t = list.get(i);
				Integer id = t.getId();
				String sum = "0";
				for (Task mt : target_mlist) {
					if(mt.getClient().equals(t.getClient()) &&
							mt.getProduct().equals(t.getProduct()) &&
							mt.getPage().equals(t.getPage()) &&
							mt.getTerminalQs().equals(t.getTerminalQs())) {
						sum = mt.getTaskM1();
						continue;
					}
				}
				target_params[i] = new Object[] {sum,id};
			}
			String target_update = "update task set quarter_actual = ? where id=?";
			query.batch(target_update, target_params);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 日志记录
	 * 什么项目、什么备注、操作花了多少时间
	 */
	private void logs(String name,String demo,String time) {
		try {
			QueryRunner query = new QueryRunner(DataSourceUtils.getDataSource());
			String sql = "insert into sys_logs(name,demo,time,updateTime) values(?,?,?,NOW())";
			query.update(sql,name,demo,time);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/***
	 * 同步注册库 定时更新 流向表
	 * 
	 * @throws SQLException
	 */
	public void update_zck_plans_policy() {
		try {
			System.out.println("同步注册库 定时更新 流向表 开始了");
			// 基础库同步注册库[省份、地市、区县] 关联更新大区、片区、经理
			QueryRunner query = new QueryRunner(DataSourceUtils.getDataSource());
			String sql_zck = "update kdb_nss_commercial_source s join kdb_nss_basis_commercial_source b on (s.country = b.country and s.city = b.city and s.shengfen=b.province) set s.mgr = b.mgr,s.province = b.daqu,s.space = b.space";
			query.update(sql_zck);

			// 注册库同步流向[注册终端、入货乙方] 关联更新： 允许更新的字段：地市、区县、分部、Qs名、级别、经理、片区、大区 若加锁则不更新
			String sql_plans1 = "UPDATE kdb_nss_log_plans p JOIN kdb_nss_commercial_source s ON (p.terminal = s.cname) SET p.city = s.city, p.county = s.country, p.fenbu = s.addr, p.zhenghe_qs = s.zhengheqs, p.terminallev = s.terminallve, p.mgr = s.mgr, p.space = s.space, p.daqu = s.province where 1=1 and status8 != 'ID' ";
			query.update(sql_plans1);
			// 非毕且没加锁时才允许更新的字段：省份
			String sql_plans11 = "UPDATE kdb_nss_log_plans p JOIN kdb_nss_commercial_source s ON (p.terminal = s.cname) SET p.province = s.shengfen where p.status1 not like '%毕%' and status8 != 'ID'";
			query.update(sql_plans11);

			// 基础库 通过【品种分组】 同步流向 p.mgr，p.space,p.daqu，经理、大区、片区
			String sql_plans2 = "UPDATE kdb_nss_log_plans p INNER JOIN kdb_nss_basis_commercial_source s ON (p.province = s.province AND p.city = s.city AND p.county = s.country AND p.product = s.product) SET p.mgr = s.mgr, p.space = s.space, p.daqu = s.daqu where 1=1 and status8 != 'ID' ";
			query.update(sql_plans2);

			// 基础库 通过【条线分组】 同步流向 p.mgr，p.space,p.daqu，经理、大区、片区
			String sql_plans3 = "UPDATE kdb_nss_log_plans p INNER JOIN kdb_nss_basis_commercial_source s ON (p.province = s.province AND p.city = s.city AND p.county = s.country AND p.xieyizhuangtai = s.product) SET p.mgr = s.mgr,p.space = s.space, p.daqu = s.daqu where 1=1 and status8 != 'ID' ";
			query.update(sql_plans3);

			// 基础库 通过【渠道分组】 同步流向 p.mgr，p.space,p.daqu经理、大区、片区
			String sql_plans4 = "UPDATE kdb_nss_log_plans p INNER JOIN kdb_nss_basis_commercial_source s ON (p.province = s.province AND p.city = s.city AND p.county = s.country AND p.terminaltype = s.product) SET p.mgr = s.mgr,p.space = s.space, p.daqu = s.daqu where 1=1 and status8 != 'ID' ";
			query.update(sql_plans4);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 定时更新销售折量 暂定： A240：10 A480：20 B5*20：0.5 B10*20：0.75（新疆）、1（其它）
	 * 
	 * @throws SQLException
	 */
	public void updateXsCountDingShi() {
		try {
			QueryRunner query = new QueryRunner(DataSourceUtils.getDataSource());
			String sql_a12 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.5,1),scount=ROUND(count * 0.5,1) where product = 'A' and pack = '12' and xieyizhuangtai = 'Rx'";
			String sql_a24 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1,1),scount=ROUND(count * 1,1) where product = 'A' and pack = '24' and xieyizhuangtai = 'Rx'";
			String sql_a36 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1.5,1),scount=ROUND(count * 1.5,1) where product = 'A' and pack = '36' and xieyizhuangtai = 'Rx'";
			String sql_a48 = "update kdb_nss_log_plans set xsScount = ROUND(count * 2,1),scount=ROUND(count * 2,1) where product = 'A' and pack = '48' and xieyizhuangtai = 'Rx'";
			String sql_a20 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.8333,1),scount=ROUND(count * 0.8333,1) where product = 'A' and pack = '20' and xieyizhuangtai = 'Rx'";
			String sql_a30 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1.25,1),scount=ROUND(count * 1.25,1) where product = 'A' and pack = '30' and xieyizhuangtai = 'Rx'";
			String sql_a40 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1.6666,1),scount=ROUND(count * 1.6666,1) where product = 'A' and pack = '40' and xieyizhuangtai = 'Rx'";
			String sql_a240 = "update kdb_nss_log_plans set xsScount = ROUND(count * 10,1),scount=ROUND(count * 10,1) where product = 'A' and pack = '240' and xieyizhuangtai = 'Rx'";
			String sql_a480 = "update kdb_nss_log_plans set xsScount = ROUND(count * 20,1),scount=ROUND(count * 20,1) where product = 'A' and pack = '480' and xieyizhuangtai = 'Rx'";
			query.update(sql_a12);
			query.update(sql_a24);
			query.update(sql_a36);
			query.update(sql_a48);
			query.update(sql_a20);
			query.update(sql_a30);
			query.update(sql_a40);
			query.update(sql_a240);
			query.update(sql_a480);
			String sql_y_24 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1,1),scount=ROUND(count * 1,1) where product = 'Y' and pack = '24' and xieyizhuangtai = 'Rx'";
			String sql_y_36 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1.5,1),scount=ROUND(count * 1.5,1) where product = 'Y' and pack = '36' and xieyizhuangtai = 'Rx'";
			String sql_y_48 = "update kdb_nss_log_plans set xsScount = ROUND(count * 2,1),scount=ROUND(count * 2,1) where product = 'Y' and pack = '48' and xieyizhuangtai = 'Rx'";
			String sql_y_20 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.8333,1),scount=ROUND(count * 0.8333,1) where product = 'Y' and pack = '20' and xieyizhuangtai = 'Rx'";
			String sql_y_30 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1.25,1),scount=ROUND(count * 1.25,1) where product = 'Y' and pack = '30' and xieyizhuangtai = 'Rx'";
			String sql_y_40 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1.6666,1),scount=ROUND(count * 1.6666,1) where product = 'Y' and pack = '40' and xieyizhuangtai = 'Rx'";
			String sql_y_60 = "update kdb_nss_log_plans set xsScount = ROUND(count * 2.5,1),scount=ROUND(count * 2.5,1) where product = 'Y' and pack = '60' and xieyizhuangtai = 'Rx'";
			query.update(sql_y_24);
			query.update(sql_y_36);
			query.update(sql_y_48);
			query.update(sql_y_20);
			query.update(sql_y_30);
			query.update(sql_y_40);
			query.update(sql_y_60);
			String sql_z_48 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1,1),scount=ROUND(count * 1,1) where product = 'Z' and pack = '48' and xieyizhuangtai = 'Rx'";
			String sql_z_24 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.5,1),scount=ROUND(count * 0.5,1) where product = 'Z' and pack = '24' and xieyizhuangtai = 'Rx'";
			String sql_z_36 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.75,1),scount=ROUND(count * 0.75,1) where product = 'Z' and pack = '36' and xieyizhuangtai = 'Rx'";
			String sql_z_20 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.4166,1),scount=ROUND(count * 0.4166,1) where product = 'Z' and pack = '20' and xieyizhuangtai = 'Rx'";
			String sql_z_30 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.625,1),scount=ROUND(count * 0.625,1) where product = 'Z' and pack = '30' and xieyizhuangtai = 'Rx'";
			String sql_z_40 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.8333,1),scount=ROUND(count * 0.8333,1) where product = 'Z' and pack = '40' and xieyizhuangtai = 'Rx'";
			String sql_z_60 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1.25,1),scount=ROUND(count * 1.25,1) where product = 'Z' and pack = '60' and xieyizhuangtai = 'Rx'";
			query.update(sql_z_48);
			query.update(sql_z_24);
			query.update(sql_z_36);
			query.update(sql_z_20);
			query.update(sql_z_30);
			query.update(sql_z_40);
			query.update(sql_z_60);
			String sql_otc_a12 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.4,1),scount=ROUND(count * 0.6,1) where product = 'A' and pack = '12' and xieyizhuangtai = 'OTC'";
			String sql_otc_a24 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.8,1),scount=ROUND(count * 1.2,1) where product = 'A' and pack = '24' and xieyizhuangtai = 'OTC'";
			String sql_otc_a36 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1.2,1),scount=ROUND(count * 1.8,1) where product = 'A' and pack = '36' and xieyizhuangtai = 'OTC'";
			String sql_otc_a48 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1.6,1),scount=ROUND(count * 2.4,1) where product = 'A' and pack = '48' and xieyizhuangtai = 'OTC'";
			String sql_otc_a20 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.6666,1),scount=ROUND(count * 1,1) where product = 'A' and pack = '20' and xieyizhuangtai = 'OTC'";
			String sql_otc_a30 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1,1),scount=ROUND(count * 1.5,1) where product = 'A' and pack = '30' and xieyizhuangtai = 'OTC'";
			String sql_otc_a40 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1.3333,1),scount=ROUND(count * 2,1) where product = 'A' and pack = '40' and xieyizhuangtai = 'OTC'";
			String sql_otc_a240 = "update kdb_nss_log_plans set xsScount = ROUND(count * 10,1),scount=ROUND(count * 10,1) where product = 'A' and pack = '240' and xieyizhuangtai = 'OTC'";
			String sql_otc_a480 = "update kdb_nss_log_plans set xsScount = ROUND(count * 20,1),scount=ROUND(count * 20,1) where product = 'A' and pack = '480' and xieyizhuangtai = 'OTC'";
			query.update(sql_otc_a12);
			query.update(sql_otc_a24);
			query.update(sql_otc_a36);
			query.update(sql_otc_a48);
			query.update(sql_otc_a20);
			query.update(sql_otc_a30);
			query.update(sql_otc_a40);
			query.update(sql_otc_a240);
			query.update(sql_otc_a480);
			String sql_otc_y24 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.4,1),scount=ROUND(count * 1.2,1) where product = 'Y' and pack = '24' and xieyizhuangtai = 'OTC'";
			String sql_otc_y36 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.6,1),scount=ROUND(count * 1.8,1) where product = 'Y' and pack = '36' and xieyizhuangtai = 'OTC'";
			String sql_otc_y48 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.8,1),scount=ROUND(count * 2.4,1) where product = 'Y' and pack = '48' and xieyizhuangtai = 'OTC'";
			String sql_otc_y20 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.3333,1),scount=ROUND(count * 1,1) where product = 'Y' and pack = '20' and xieyizhuangtai = 'OTC'";
			String sql_otc_y30 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.5,1),scount=ROUND(count * 1.5,1) where product = 'Y' and pack = '30' and xieyizhuangtai = 'OTC'";
			String sql_otc_y40 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.6666,1),scount=ROUND(count * 2,1) where product = 'Y' and pack = '40' and xieyizhuangtai = 'OTC'";
			String sql_otc_y60 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1,1),scount=ROUND(count * 3,1) where product = 'Y' and pack = '60' and xieyizhuangtai = 'OTC'";
			query.update(sql_otc_y24);
			query.update(sql_otc_y36);
			query.update(sql_otc_y48);
			query.update(sql_otc_y20);
			query.update(sql_otc_y30);
			query.update(sql_otc_y40);
			query.update(sql_otc_y60);
			String sql_otc_z48 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.8,1),scount=ROUND(count * 2.4,1) where product = 'Z' and pack = '48' and xieyizhuangtai = 'OTC'";
			String sql_otc_z24 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.4,1),scount=ROUND(count * 1.2,1) where product = 'Z' and pack = '24' and xieyizhuangtai = 'OTC'";
			String sql_otc_z36 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.6,1),scount=ROUND(count * 1.8,1) where product = 'Z' and pack = '36' and xieyizhuangtai = 'OTC'";
			String sql_otc_z20 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.3333,1),scount=ROUND(count * 1,1) where product = 'Z' and pack = '20' and xieyizhuangtai = 'OTC'";
			String sql_otc_z30 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.5,1),scount=ROUND(count * 1.5,1) where product = 'Z' and pack = '30' and xieyizhuangtai = 'OTC'";
			String sql_otc_z40 = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.6666,1),scount=ROUND(count * 2,1) where product = 'Z' and pack = '40' and xieyizhuangtai = 'OTC'";
			String sql_otc_z60 = "update kdb_nss_log_plans set xsScount = ROUND(count * 1,1),scount=ROUND(count * 3,1) where product = 'Z' and pack = '60' and xieyizhuangtai = 'OTC'";
			query.update(sql_otc_z48);
			query.update(sql_otc_z24);
			query.update(sql_otc_z36);
			query.update(sql_otc_z20);
			query.update(sql_otc_z30);
			query.update(sql_otc_z40);
			query.update(sql_otc_z60);
			
			//非新疆
			String b5_other = "update kdb_nss_log_plans set xsScount = ROUND(count * 1,1),scount=ROUND(count * 1,1) where product = 'B' and pack = '5' and province != '新疆'";
			String b10_other = "update kdb_nss_log_plans set xsScount = ROUND(count * 2,1),scount=ROUND(count * 2,1) where product = 'B' and pack = '10' and province != '新疆'";
			//新疆
			String b5_xj_zy = "update kdb_nss_log_plans set xsScount = ROUND(count * 1,1) where product = 'B' and pack = '5' and province = '新疆' and mode='自营'";
			String b10_xj_zy = "update kdb_nss_log_plans set xsScount = ROUND(count * 2,1) where product = 'B' and pack = '10' and province = '新疆' and mode='自营'";
			
			String b5_xj_xb = "update kdb_nss_log_plans set xsScount = ROUND(count * 1,1) where product = 'B' and pack = '5' and province = '新疆' and mode='小包'";
			String b10_xj_xb = "update kdb_nss_log_plans set xsScount = ROUND(count * 1,1) where product = 'B' and pack = '10' and province = '新疆' and mode='小包'";
			
			String b5_xj_yf = "update kdb_nss_log_plans set xsScount = ROUND(count * 0.5,1) where product = 'B' and pack = '5' and province = '新疆' and mode='预付'";
			String b10_xj_yf = "update kdb_nss_log_plans set xsScount = ROUND(count * 1,1) where product = 'B' and pack = '10' and province = '新疆' and mode='预付'";
			
			
			query.update(b5_xj_zy);
			query.update(b10_xj_zy);
			query.update(b5_xj_xb);
			query.update(b10_xj_xb);
			query.update(b5_xj_yf);
			query.update(b10_xj_yf);
			query.update(b5_other);
			query.update(b10_other);
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	/***
	 * 同步流向操作 1、同步重点终端 到流向表 2、同步好伙伴到流向表 3、同步折量
	 */
	public void tongbu() {
		try {
			QueryRunner query = new QueryRunner(DataSourceUtils.getDataSource());
			// 1、同步重点终端 到流向表
			String focuson_sql = "update kdb_nss_log_plans p join kdb_nss_focuson f " + "on(p.zhenghe_qs = f.qs) "
					+ " set focusonFlag = f.fenzu " + " where p.`year` = '" + DateUtils.getCurrentYear()
					+ "' and p.judge = '纯销'";
			query.update(focuson_sql);
			// 2、同步好伙伴到流向表
			String good_sql = "update kdb_nss_log_plans p join kdb_nss_goodfriend f " + " on(p.daibiao = f.name) "
					+ " set p.goodFriendFlag = f.fenzu " + " where p.`year` = '" + DateUtils.getCurrentYear()
					+ "' and p.judge = '纯销'";
			query.update(good_sql);
			// 3、同步折量
			updateXsCountDingShi();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/***
	 * 成品成本
	 */
	public void tongbu_cost() {
		try {
			QueryRunner query = new QueryRunner(DataSourceUtils.getDataSource());
			String sql_a12 = "update kdb_nss_log_plans set cost = "+Constant.COST_A12+" where product = 'A' and pack = '12'";
			String sql_a24 = "update kdb_nss_log_plans set cost = "+Constant.COST_A24+" where product = 'A' and pack = '24'";
			String sql_a36 = "update kdb_nss_log_plans set cost = "+Constant.COST_A36+" where product = 'A' and pack = '36'";
			String sql_a48 = "update kdb_nss_log_plans set cost = "+Constant.COST_A48+" where product = 'A' and pack = '48'";
			String sql_a20 = "update kdb_nss_log_plans set cost = "+Constant.COST_A20+" where product = 'A' and pack = '20'";
			String sql_a30 = "update kdb_nss_log_plans set cost = "+Constant.COST_A30+" where product = 'A' and pack = '30'";
			String sql_a40 = "update kdb_nss_log_plans set cost = "+Constant.COST_A40+" where product = 'A' and pack = '40'";
			String sql_a240 = "update kdb_nss_log_plans set cost = "+Constant.COST_A240+" where product = 'A' and pack = '240'";
			String sql_a480 = "update kdb_nss_log_plans set cost = "+Constant.COST_A480+" where product = 'A' and pack = '480'";
			query.update(sql_a12);
			query.update(sql_a24);
			query.update(sql_a36);
			query.update(sql_a48);
			query.update(sql_a20);
			query.update(sql_a30);
			query.update(sql_a40);
			query.update(sql_a240);
			query.update(sql_a480);
			String sql_y_24 = "update kdb_nss_log_plans set cost = "+Constant.COST_Y24+" where product = 'Y' and pack = '24'";
			String sql_y_36 = "update kdb_nss_log_plans set cost = "+Constant.COST_Y36+" where product = 'Y' and pack = '36'";
			String sql_y_20 = "update kdb_nss_log_plans set cost = "+Constant.COST_Y20+" where product = 'Y' and pack = '20'";
			String sql_y_30 = "update kdb_nss_log_plans set cost = "+Constant.COST_Y30+" where product = 'Y' and pack = '30'";
			String sql_y_40 = "update kdb_nss_log_plans set cost = "+Constant.COST_Y40+" where product = 'Y' and pack = '40'";
			query.update(sql_y_24);
			query.update(sql_y_36);
			query.update(sql_y_20);
			query.update(sql_y_30);
			query.update(sql_y_40);
			String sql_z_48 = "update kdb_nss_log_plans set cost = "+Constant.COST_Z48+" where product = 'Z' and pack = '48'";
			String sql_z_20 = "update kdb_nss_log_plans set cost = "+Constant.COST_Z20+" where product = 'Z' and pack = '20'";
			String sql_z_30 = "update kdb_nss_log_plans set cost = "+Constant.COST_Z30+" where product = 'Z' and pack = '30'";
			String sql_z_24 = "update kdb_nss_log_plans set cost = "+Constant.COST_Z24+" where product = 'Z' and pack = '24'";
			String sql_z_36 = "update kdb_nss_log_plans set cost = "+Constant.COST_Z24+" where product = 'Z' and pack = '36'";
			String sql_z_40 = "update kdb_nss_log_plans set cost = "+Constant.COST_Z40+" where product = 'Z' and pack = '40'";
			query.update(sql_z_48);
			query.update(sql_z_24);
			query.update(sql_z_36);
			query.update(sql_z_20);
			query.update(sql_z_30);
			query.update(sql_z_40);
			String b5 = "update kdb_nss_log_plans set cost = "+Constant.COST_B5+" where product = 'B' and pack = '5'";
			String b10 = "update kdb_nss_log_plans set cost = "+Constant.COST_B10+" where product = 'B' and pack = '10'";
			query.update(b5);
			query.update(b10);
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	/***
	 * 系统自动 【非毕纯销流向】 匹配4/5/6状态协议 匹配当年和上一年的流向 能找到就匹配，找不到就不更新
	 * 
	 * @throws SQLException
	 */
	public void systemAutoPPPolicy(){
		try {
			QueryRunner query = new QueryRunner(DataSourceUtils.getDataSource());
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
			String year = sdf.format(new Date());
			int y = Integer.parseInt(year);
			// 查询出所有的非毕纯销
			String query_plans_sql = "select * from kdb_nss_log_plans where 1=1 and judge ='纯销' and status1 not like '%毕%' and year in('"
					+ (y - 1) + "','" + y + "')";
			/***
			 * 匹配流向（纯销）
			 */
			List<Plan> plans = new ArrayList<Plan>(88888);
			plans.addAll(query.query(query_plans_sql, new BeanListHandler<Plan>(Plan.class)));
			List<Policy> policys = new ArrayList<Policy>(88888);
			String query_policy_sql = " select * from kdb_nss_log_client_policy where 1=1 and guihuanriqi in ('4','5','6') ";
			policys.addAll(query.query(query_policy_sql, new BeanListHandler<Policy>(Policy.class)));
			if (plans != null && plans.size() >= 1) {
				for (Plan p : plans) {
					for (Policy po : policys) {
						if (p.getProduct().equals(po.getProduct()) && p.getPack().equals(po.getPage())
								&& p.getTerminal().equals(po.getQsterminal())
								&& !"手动匹配政策".equals(p.getStatus7())) {
							// 协议开始时间<=流向时间<协议结束时间
							if (isGt2Eq(p.getSdate(), po.getXieyistarttime()) && isLt(p.getSdate(), po.getXieyiendtime())) {
								init_policyed_logs_dingshi(query, p, po);
								continue;
							}
						}
					}
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	/***
	 * 系统自动 【非毕流向】 特别是手动匹配的流向、和销售流向的政策统一
	 * 
	 * @throws SQLException
	 */
	public void systemAutoPPPolicyById(){
		try {
			QueryRunner query = new QueryRunner(DataSourceUtils.getDataSource());
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
			String year = sdf.format(new Date());
			int y = Integer.parseInt(year);
			// 查询出所有的非毕销售
			String query_plans_sql = "select * from kdb_nss_log_plans where 1=1 and (judge ='销售' or status7='手动匹配政策') and status1 not like '%毕%' and year in('"
					+ (y - 1) + "','" + y + "')";
			/***
			 * 匹配流向
			 */
			List<Plan> plans = new ArrayList<Plan>(88888);
			plans.addAll(query.query(query_plans_sql, new BeanListHandler<Plan>(Plan.class)));
			List<Policy> policys = new ArrayList<Policy>(88888);
			String query_policy_sql = " select * from kdb_nss_log_client_policy where 1=1 and guihuanriqi in ('4','5','6') ";
			policys.addAll(query.query(query_policy_sql, new BeanListHandler<Policy>(Policy.class)));
			if (plans != null && plans.size() >= 1) {
				for (Plan p : plans) {
					for (Policy po : policys) {
						if (po.getId().toString().equals(p.getClientpolicyId())) {
							init_policyed_logs_dingshi(query, p, po);
							continue;
						}
					}
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	/***
	 * 两个时间比较，是否大于等于
	 * 
	 * @param t1
	 * @param t2
	 * @return
	 */
	private static boolean isGt2Eq(String t1, String t2) {
		SimpleDateFormat SDF = new SimpleDateFormat("yyyy/MM/dd");
		try {
			Date d1 = SDF.parse(t1);
			Date d2 = SDF.parse(t2);
			if (d1.getTime() >= d2.getTime()) {
				return true;
			}
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return false;
	}

	/***
	 * 两个时间比较，是否小于
	 * 
	 * @param t1
	 * @param t2
	 * @return
	 */
	private static boolean isLt(String t1, String t2) {
		SimpleDateFormat SDF = new SimpleDateFormat("yyyy/MM/dd");
		try {
			Date d1 = SDF.parse(t1);
			Date d2 = SDF.parse(t2);
			if (d1.getTime() < d2.getTime()) {
				return true;
			}
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return false;
	}

	/***
	 * 循环迭代当年以及上一年的纯销流向，系统定时匹配 协议【4/5/6状态】
	 * 
	 * @param query
	 * @param log
	 * @param p
	 * @throws SQLException
	 */
	private void init_policyed_logs_dingshi(QueryRunner query, Plan log, Policy p) {
		try {
			if(p != null) {
				log.setZhongbiaojiamoney(money(p.getZhongbiaojia(), log.getCount()));// 中标金额
				log.setPeisonglv(caculater(p.getKaipiaojia(), p.getZhongbiaojia()));// 配送率
				log.setKaipiaojiamoney(money(p.getKaipiaojia(), log.getCount()));// 开票金额
				log.setBasic1(p.getTuiguangmoney());// 1推广费
				log.setMoney1(money(p.getTuiguangmoney(), log.getCount()));// 1推广金额
				log.setPiaoju3(p.getFapiaomoney());// 3发票服务费
				log.setMoney3(money(p.getFapiaomoney(), log.getCount()));// 3发票金额
				log.setDabiao4(p.getDaibiaomoney());// 4达标费用
				log.setMoney4(money(p.getDaibiaomoney(), log.getCount()));// 达标金额
				log.setZhangdan1(getJsZhangdan(log, p));// 结算账单
				log.setDaibiao(p.getDaibiao());// 代表
				log.setShoukuangren(p.getShoukuangren());// 收款人
				log.setKaihuhang(p.getKaihuhang());// 开户行
				log.setZhanghao(p.getZhanghao());// 账号
				log.setKaipiaofangshi(p.getKaipiaofangshi());// 票据方案
				// 总费用
				log.setTotalmoney(total(log.getMoney1(), log.getMoney3(), log.getMoney4(), "0"));
				log.setAtsum(p.getDijia());// 协议测算价
				log.setYingyumoney(p.getBiaozhuncsdj());// 标准测算价
				log.setYinyukaiguan(p.getKaipiaojia());// 开票价
				log.setCuxiao_ed_money(p.getZhongbiaojia());// 中标价
				log.setJsmode(p.getJsmode());// 结算方式
				// 中标费用率
				log.setFeiyonglv(ArithUtil.percent(log.getTotalmoney(), log.getZhongbiaojiamoney()));
				log.setClientpolicyId(p.getId() + "");// 协议id
				log.setPolicystatus(p.getGuihuanriqi());// 协议状态
				if (p.getZhanghao() != null && p.getZhanghao().contains(Constant.BANK_CARD)) {// 银行账号
					log.setYhzhanghao(StringUtil.formatStringGetNumber(p.getZhanghao()));
				}
				// log.setQsterminal(p.getQsterminal());// 协议终端
				log.setMode(p.getMode());// 协议模式，流向以为可以录入模式，但是有协议就参考协议的
				log.setTerminaltype(p.getTerminaltype());// 渠道 默认参考注册库，有协议就参考协议的
				log.setXieyizhuangtai(p.getQudaolie());// 条线 默认参考注册库，有协议就参考协议的
				log.setProvince(p.getProvince());//省份 默认参考注册库，有协议就参考协议的
				log.setClient(p.getClient());// 协议客户

				String sql = "update kdb_nss_log_plans set province=?,terminaltype=?,xieyizhuangtai=?,kaipiaofangshi=?,"
						+ "shoukuangren=?,kaihuhang=?,zhanghao=?,client=?,daibiao=?,"
						+ "mode=?,basic1=?,money1=?,piaoju3=?,money3=?,dabiao4=?,"
						+ "money4=?,zhongbiaojiamoney=?,peisonglv=?,kaipiaojiamoney=?,"
						+ "yingyumoney=?,atsum=?,jsmode=?,zhangdan1=?,clientpolicyId=?,"
						+ "policystatus=?,totalmoney=?,yhzhanghao=?,yinyukaiguan=?,"
						+ "cuxiao_ed_money=? where id=? and status1 not like '%毕%' ";
				
				query.update(sql,log.getProvince(),log.getTerminaltype(),log.getXieyizhuangtai(),log.getKaipiaofangshi(), 
						log.getShoukuangren(),log.getKaihuhang(),log.getZhanghao(),log.getClient(),log.getDaibiao(),
						log.getMode(), log.getBasic1(),
						log.getMoney1(), log.getPiaoju3(),
						log.getMoney3(), log.getDabiao4(),
						log.getMoney4(), log.getZhongbiaojiamoney(),
						log.getPeisonglv(), log.getKaipiaojiamoney(),
						log.getYingyumoney(), log.getAtsum(), log.getJsmode(), 
						log.getZhangdan1(), log.getClientpolicyId(),
						log.getPolicystatus(), log.getTotalmoney(), log.getYhzhanghao(),
						log.getYinyukaiguan(), log.getCuxiao_ed_money(), log.getId());
			}
			
			if (p!=null) {
				//5开发奖励
				log.setKaifa5(p.getKaifajianlishixiang());
				log.setMoney5(ArithUtil.money(p.getKaifajianlishixiang(),log.getCount()));
				String sql = "update kdb_nss_log_plans set kaifa5=?,money5=? where id=? and status5 not like '%毕%' ";
				query.update(sql, log.getKaifa5(),log.getMoney5(),log.getId());
			}
			if (p!=null) {
				//业务员开发奖励,录入协议时，需要 录入人员等比折算
				log.setKaifaMgr(p.getKaifaMgr());
				log.setMoneyMgr(ArithUtil.money(p.getKaifaMgr(),log.getCount()));
				String sql = "update kdb_nss_log_plans set kaifaMgr=?,moneyMgr=? where id=? and statusMgr not like '%毕%' ";
				query.update(sql, log.getKaifaMgr(),log.getMoneyMgr(),log.getId());
			} 
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	private String money(String m, String count) {
		DecimalFormat df = new DecimalFormat("0.00");
		df.setRoundingMode(RoundingMode.HALF_UP);
		String mymoney = "";
		try {
			float money = 0;
			float tuig = 0;
			tuig = new Float(m.trim());
			money = new Float(count.trim()) * tuig;
			mymoney = df.format(money);
		} catch (Exception e) {
			mymoney = "0.00";
		}
		return mymoney;
	}

	private String total(String money1, String money3, String money4, String kaifajianglimoney) {
		DecimalFormat df = new DecimalFormat("0.00");
		df.setRoundingMode(RoundingMode.HALF_UP);
		float tuiguang = new Float(money1);
		float piaoju = new Float(money3);
		float dabiao = new Float(money4);
		float kaifajiangli = new Float(kaifajianglimoney);
		float total = tuiguang + piaoju + dabiao + kaifajiangli;
		return df.format(total);
	}

	private String caculater(String m1, String m2) {
		DecimalFormat df = new DecimalFormat("0");
		df.setRoundingMode(RoundingMode.HALF_UP);
		float chushu = new Float(m1.trim());
		float beichushu = new Float(m2.trim());
		float result = chushu / beichushu * 100;
		return df.format(result) + "%";
	}

	private String getJsZhangdan(Plan plan, Policy p) {
		String zhangdan = "";
		if (p.getJsmode().contains("次月结")) {
			String currentyear = plan.getYear();
			String currentmouth = plan.getMouth();
			String year = "";
			String mouth = "";
			if (currentmouth.contains("12")) {
				year = "Y" + (new Integer(currentyear) + 1);
				mouth = "M01";
			} else {
				year = "Y" + currentyear;
				if (new Integer(currentmouth) < 9) {
					mouth = "M0" + (new Integer(currentmouth) + 1);
				} else {
					mouth = "M" + (new Integer(currentmouth) + 1);
				}
			}
			zhangdan = year + mouth;
		}
		if (p.getJsmode().contains("押1月结")) {
			String currentyear = plan.getYear();
			String currentmouth = plan.getMouth();
			String year = "";
			String mouth = "";
			if (currentmouth.contains("12")) {
				year = "Y" + (new Integer(currentyear) + 1);
				mouth = "M02";
			} else if (currentmouth.contains("11")) {
				year = "Y" + (new Integer(currentyear) + 1);
				mouth = "M01";
			} else {
				year = "Y" + currentyear;
				if (new Integer(currentmouth) < 8) {
					mouth = "M0" + (new Integer(currentmouth) + 2);
				} else {
					mouth = "M" + (new Integer(currentmouth) + 2);
				}
			}
			zhangdan = year + mouth;
		}
		if (p.getJsmode().contains("押2月结")) {
			String currentyear = plan.getYear();
			String currentmouth = plan.getMouth();
			String year = "";
			String mouth = "";
			if (currentmouth.contains("12")) {
				year = "Y" + (new Integer(currentyear) + 1);
				mouth = "M03";
			} else if (currentmouth.contains("11")) {
				year = "Y" + (new Integer(currentyear) + 1);
				mouth = "M02";
			} else if (currentmouth.contains("10")) {
				year = "Y" + (new Integer(currentyear) + 1);
				mouth = "M01";
			} else {
				year = "Y" + currentyear;
				if (new Integer(currentmouth) < 7) {
					mouth = "M0" + (new Integer(currentmouth) + 3);
				} else {
					mouth = "M" + (new Integer(currentmouth) + 3);
				}
			}
			zhangdan = year + mouth;
		}
		return zhangdan;
	}

	/***
	 * 系统自动流向匹配自然流 匹配当年和上一年的流向
	 * 
	 * @throws SQLException
	 *             【纯销、销售】流向的协议id，在协议表中找不到则设置为自然流向
	 */
	public void systemAutoPPNoPolicy() {
		try {
			QueryRunner query = new QueryRunner(DataSourceUtils.getDataSource());
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
			String year = sdf.format(new Date());
			int y = Integer.parseInt(year);
			List<PolicyIdItem> planPids = new ArrayList<PolicyIdItem>(88888);
			List<PolicyIdItem> pids = new ArrayList<PolicyIdItem>(88888);
			// 查询出所有的非毕纯销和销售流向
			String query_plans_sql = "select * from kdb_nss_log_plans where 1=1 and judge in('纯销','销售') and status1 not like '%毕%' and year in('"
					+ (y - 1) + "','" + y + "')";
			/***
			 * 匹配流向【纯销、销售】
			 */
			List<Plan> plans = new ArrayList<Plan>(88888);
			plans.addAll(query.query(query_plans_sql, new BeanListHandler<Plan>(Plan.class)));
			List<Policy> policys = new ArrayList<Policy>(88888);
			String query_policy_sql = " select * from kdb_nss_log_client_policy where 1=1 and guihuanriqi in('4','5','6') ";
			policys.addAll(query.query(query_policy_sql, new BeanListHandler<Policy>(Policy.class)));

			for (Plan p : plans) {
				PolicyIdItem pitem = new PolicyIdItem(p.getId() + "", p.getClientpolicyId() + "");
				planPids.add(pitem);
			}
			for (Policy po : policys) {
				PolicyIdItem pitem = new PolicyIdItem("", po.getId() + "");
				pids.add(pitem);
			}
			// 取交集后，剩下的流向,对应的协议id在协议表里找不到有效的
			planPids.removeAll(pids);
			if (!planPids.isEmpty()) {
				StringBuffer sb = new StringBuffer();
				for (PolicyIdItem p : planPids) {
					if (p.getPoid() != null && !p.getPoid().equals("0")) {
						sb.append(p.getPid() + ",");
					}
				}
				if (sb.toString().contains(",")) {
					sb.deleteCharAt(sb.length() - 1);
				}
				if (StringUtils.isNotBlank(sb.toString())) {
					String sql = "update kdb_nss_log_plans set kaipiaofangshi='',"
							+ "shoukuangren='',kaihuhang='',zhanghao='',"
							+ "basic1='0',money1='0',piaoju3='0',money3='0',dabiao4='0',"
							+ "money4='0',zhongbiaojiamoney='0',peisonglv='0',kaipiaojiamoney='0',"
							+ "yingyumoney='0',atsum='0',jsmode='',zhangdan1='',clientpolicyId='0',"
							+ "policystatus='0',totalmoney='0',yhzhanghao='',yinyukaiguan='0'," + "cuxiao_ed_money='0', "
							+ "status1='押',status3='押',status4='押', "
							+ "daibiao='',moneybill1='',feiyonglv='0',tidanfangshi='' where id in(" + sb.toString() + ") "
							+ " and status1 not like '%毕%' ";
					query.update(sql);

					// 5开发奖励
					String sql2 = "update kdb_nss_log_plans set kaifa5='0',money5='0',status5='押' where id in("
							+ sb.toString() + ") " + " and status5 not like '%毕%' ";
					query.update(sql2);

					// 经理开发奖励
					String sql3 = "update kdb_nss_log_plans set kaifaMgr='0',moneyMgr='0',statusMgr='押' where id in("
							+ sb.toString() + ") " + " and statusMgr not like '%毕%' ";
					query.update(sql3);
				}
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
	}

	/***
	 * 更新协议id， 协议id=-1，表示真自然流 协议id=0，表示没匹配协议
	 * 
	 * @throws SQLException
	 */
	public void updatePolicyIdDingShi() {
		try {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
			String year = sdf.format(new Date());
			int y = Integer.parseInt(year);
			int p_y = y - 1;

			QueryRunner query = new QueryRunner(DataSourceUtils.getDataSource());
			String sql = "update kdb_nss_log_plans set clientpolicyId = '-1' where status6 like '%真自然流%' and year in ('"
					+ p_y + "','" + year + "') ";
			query.update(sql);
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	public static void main(String[] args) {
		ExpireJobTask ex = new ExpireJobTask ();
		long start = System.currentTimeMillis();
		ex.systemAutoPPPolicy();// 同步更新产品成本
		//ex.updateXsCountDingShi();
		long end = System.currentTimeMillis();
		//ex.logs("同步更新产品成本","成功执行",(end-start)/1000+"秒");
		System.out.println((end-start)/1000+"秒");
	}
}
